﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using DP.Sharepoint.SiteWorkflow.SSIS;
using System.Xml;
using System.Collections.Generic;
using System.Security.Principal;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int StartSharepointSiteWorkflow(SqlString siteUrl, SqlString workflowName)
    {
        WindowsIdentity newId = SqlContext.WindowsIdentity;
        WindowsImpersonationContext impersonatedUser = newId.Impersonate();

        try
        {
            using (DeclarativeSiteWorkflowAssistant assistant = new DeclarativeSiteWorkflowAssistant(new Uri(siteUrl.Value)))
            {
                assistant.StartDeclarativeSiteWorkflow(new SiteWorkflowActivationWS.SiteWorkflowInfo() { WorkflowName = workflowName.Value });
            }
        }
        finally
        {
            impersonatedUser.Undo();
        }
        // Put your code here
        return 0;
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int StartSharepointDeclarativeSiteWorkflow(SqlString siteUrl, SqlString workflowName, SqlXml paramterXml)
    {


        string sampleXml = "<WorkflowParameters><WorkflowParameter Name=\"Pram1\" Value=\"Value1\"/><WorkflowParameter Name=\"Pram2\" Value=\"Value2\"/></WorkflowParameters>";

        List<SiteWorkflowActivationWS.SiteWorkflowParameterInfo> wpl = new List<SiteWorkflowActivationWS.SiteWorkflowParameterInfo>();

        XmlDocument xdoc = new XmlDocument();

        try
        {
            xdoc.LoadXml(paramterXml.Value);
        }
        catch
        {
            throw new Exception("[xdoc.LoadXml]Invalid parameterXml passed in! Example Format: " + sampleXml);

        }

        if (xdoc.DocumentElement.Name != "WorkflowParameters")
            throw new Exception("Invalid parameterXml passed in! Example Format: " + sampleXml);

        foreach (XmlNode item in xdoc.SelectNodes("/WorkflowParameters/WorkflowParameter"))
        {

            try
            {

                wpl.Add(new SiteWorkflowActivationWS.SiteWorkflowParameterInfo() { Name = item.Attributes["Name"].InnerText, Value = item.Attributes["Value"].InnerText });

            }
            catch
            {
                throw new Exception("[xdoc.SelectNodes]Invalid parameterXml passed in! Example Format: " + sampleXml);


            }
        }

        WindowsIdentity newId = SqlContext.WindowsIdentity;
        WindowsImpersonationContext impersonatedUser = newId.Impersonate();

        try
        {

            using (DeclarativeSiteWorkflowAssistant assistant = new DeclarativeSiteWorkflowAssistant(new Uri(siteUrl.Value)))
            {
                assistant.StartDeclarativeSiteWorkflow(new SiteWorkflowActivationWS.SiteWorkflowInfo() { WorkflowName = workflowName.Value, WorkflowParameters = wpl.ToArray() });
            }

        }
        finally
        {
            impersonatedUser.Undo();
        }
        // Put your code here
        return 0;
    }
};
